Clone Method Example

This example uses the Clone method to create copies of a Recordset and then lets the user position the record pointer of each copy independently.

Sub CloneX()

    Dim dbsNorthwind As Database
    Dim arstProducts(1 To 3) As Recordset
    Dim intLoop As Integer
    Dim strMessage As String
    Dim strFind As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' If the following SQL statement will be used often, 
    ' creating a permanent QueryDef will result in better
    ' performance.
    Set arstProducts(1) = dbsNorthwind.OpenRecordset( _
        "SELECT ProductName FROM Products " & _
        "ORDER BY ProductName", dbOpenSnapshot)

    ' Create two clones of the original Recordset.
    Set arstProducts(2) = arstProducts(1).Clone
    Set arstProducts(3) = arstProducts(1).Clone

    Do While True

        ' Loop through the array so that on each pass, the 
        ' user is searching a different copy of the same 
        ' Recordset.
        For intLoop = 1 To 3

            ' Ask for search string while showing where the
            ' current record pointer is for each Recordset.
            strMessage = _
                "Recordsets from Products table:" & vbCr & _
                "  1 - Original - Record pointer at " & _
                arstProducts(1)!ProductName & vbCr & _
                "  2 - Clone - Record pointer at " & _
                arstProducts(2)!ProductName & vbCr & _
                "  3 - Clone - Record pointer at " & _
                arstProducts(3)!ProductName & vbCr & _
                "Enter search string for #" & intLoop & ":"
            strFind = Trim(InputBox(strMessage))
            If strFind = "" Then Exit Do

            ' Find the search string; if there's no match, jump
            ' to the last record.
            With arstProducts(intLoop)
                .FindFirst "ProductName >= '" & strFind & "'"
                If .NoMatch Then .MoveLast
            End With

        Next intLoop

    Loop

    arstProducts(1).Close
    arstProducts(2).Close
    arstProducts(3).Close
    dbsNorthwind.Close

End Sub